Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

JOINs

Table of Contents

  1. Basic Joins

  2. Advanced Joins

  3. Multiple Table Joins (4 Tables)

1. Basic Joins

1.1 No Join

Task: Retrieve all data from the customers and orders tables as separate result sets (no join between them).

💡 Answer 
-- No Join
/* Retrieve all data from customers and orders as separate results */
SELECT * FROM customers;
SELECT * FROM orders;

1.2 INNER JOIN

Task: Get all customers along with their orders, but only for customers who have placed at least one order.

💡 Answer 
-- INNER JOIN
/* Get all customers along with their orders, 
   but only for customers who have placed an order */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.sales
FROM customers AS c
INNER JOIN orders AS o
ON c.id = o.customer_id;

1.3 LEFT JOIN

Task: Get all customers along with their orders, including customers who have not placed any order.

💡 Answer 
-- LEFT JOIN
/* Get all customers along with their orders, 
   including those without orders */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.sales
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id;

1.4 RIGHT JOIN

Task: Get all customers along with their orders, including orders that don’t have a matching customer.

💡 Answer 
-- RIGHT JOIN
/* Get all customers along with their orders, 
   including orders without matching customers */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.customer_id,
    o.sales
FROM customers AS c 
RIGHT JOIN orders AS o 
ON c.id = o.customer_id;

1.5 Alternative to RIGHT JOIN using LEFT JOIN

Task: Rewrite the previous RIGHT JOIN query using a LEFT JOIN instead, while keeping the same logic (include orders without matching customers).

💡 Answer 
-- Alternative to RIGHT JOIN using LEFT JOIN
/* Get all customers along with their orders, 
   including orders without matching customers */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.sales
FROM orders AS o 
LEFT JOIN customers AS c
ON c.id = o.customer_id;

1.6 FULL JOIN

Task: Get all customers and all orders, even if there is no match between them.

💡 Answer 
-- FULL JOIN
/* Get all customers and all orders, even if there’s no match */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.customer_id,
    o.sales
FROM customers AS c 
FULL JOIN orders AS o 
ON c.id = o.customer_id;

2. Advanced Joins

2.1 LEFT ANTI JOIN

Task: Get all customers who haven’t placed any order (customers with no matching order).

💡 Answer 
-- LEFT ANTI JOIN
/* Get all customers who haven't placed any order */
SELECT *
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

2.2 RIGHT ANTI JOIN

Task: Get all orders that don’t have matching customers (orders with no valid customer).

💡 Answer 
-- RIGHT ANTI JOIN
/* Get all orders without matching customers */
SELECT *
FROM customers AS c
RIGHT JOIN orders AS o
ON c.id = o.customer_id
WHERE c.id IS NULL;

2.3 Alternative to RIGHT ANTI JOIN using LEFT JOIN

Task: Rewrite the previous RIGHT ANTI JOIN query using a LEFT JOIN, still returning orders without matching customers.

💡 Answer 
-- Alternative to RIGHT ANTI JOIN using LEFT JOIN
/* Get all orders without matching customers */
SELECT *
FROM orders AS o 
LEFT JOIN customers AS c
ON c.id = o.customer_id
WHERE c.id IS NULL;

2.4 Alternative to INNER JOIN using LEFT JOIN

Task: Using a LEFT JOIN + WHERE filter, return all customers along with their orders, but only for customers who have placed an order (i.e., replicate INNER JOIN behaviour).

💡 Answer 
-- Alternative to INNER JOIN using LEFT JOIN
/* Get all customers along with their orders, 
   but only for customers who have placed an order */
SELECT *
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
WHERE o.customer_id IS NOT NULL;

2.5 FULL ANTI JOIN

Task: Find all customers without orders and all orders without customers in a single result set.

💡 Answer 
-- FULL ANTI JOIN
/* Find customers without orders and orders without customers */
SELECT
    c.id,
    c.first_name,
    o.order_id,
    o.customer_id,
    o.sales
FROM customers AS c 
FULL JOIN orders AS o 
ON c.id = o.customer_id
WHERE o.customer_id IS NULL OR c.id IS NULL;

2.6 CROSS JOIN

Task: Generate all possible combinations of customers and orders (Cartesian product).

💡 Answer 
-- CROSS JOIN
/* Generate all possible combinations of customers and orders */
SELECT *
FROM customers
CROSS JOIN orders;

3. Multiple Table Joins (4 Tables)

3.1 Orders with Customer, Product, and Employee Details

Task: Using the SalesDB database, retrieve a list of all orders along with related customer, product, and employee details. For each order, display:

  • Order ID

  • Customer’s name

  • Product name

  • Sales amount

  • Product price

  • Salesperson’s name

    💡 Answer 
    /* Task: Using SalesDB, Retrieve a list of all orders, along with the related customer, product, 
   and employee details. For each order, display:
   - Order ID
   - Customer's name
   - Product name
   - Sales amount
   - Product price
   - Salesperson's name */

USE SalesDB;

SELECT 
    o.OrderID,
    o.Sales,
    c.FirstName AS CustomerFirstName,
    c.LastName AS CustomerLastName,
    p.Product AS ProductName,
    p.Price,
    e.FirstName AS EmployeeFirstName,
    e.LastName AS EmployeeLastName
FROM Sales.Orders AS o
LEFT JOIN Sales.Customers AS c
ON o.CustomerID = c.CustomerID
LEFT JOIN Sales.Products AS p
ON o.ProductID = p.ProductID
LEFT JOIN Sales.Employees AS e
ON o.SalesPersonID = e.EmployeeID;